﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Dapper;

namespace LibraryManagementSystem
{
    /// <summary>
    /// 数据库配置类，包含MySQL连接字符串和初始化方法
    /// </summary>
    public class DatabaseConfig
    {
        // MySQL连接字符串
        private const string ConnectionString = "Server=192.168.1.99;Database=librarymanagement;User=root;Password=shift_!@#123;";

        /// <summary>
        /// 初始化数据库，创建必要的表和初始数据
        /// </summary>
        public static void InitializeDatabase()
        {
            using (var connection = new MySqlConnection(ConnectionString))
            {
                connection.Open();

                // 检查Users表是否存在，不存在则创建
                string checkTableExists = @"
                CREATE TABLE IF NOT EXISTS Users (
                    Id INT PRIMARY KEY AUTO_INCREMENT,
                    Username VARCHAR(50) UNIQUE NOT NULL,
                    PasswordHash VARCHAR(100) NOT NULL,
                    Salt VARCHAR(50) NOT NULL,
                    Name VARCHAR(50) NOT NULL,
                    Email VARCHAR(100),
                    Phone VARCHAR(20),
                    IsAdmin TINYINT(1) NOT NULL DEFAULT 0,
                    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
                )";

                connection.Execute(checkTableExists);

                // 创建图书分类表
                string createCategoryTable = @"
                CREATE TABLE IF NOT EXISTS BookCategories (
                    Id INT PRIMARY KEY AUTO_INCREMENT,
                    CategoryCode VARCHAR(20) NOT NULL UNIQUE,
                    CategoryName VARCHAR(100) NOT NULL,
                    ParentId INT NULL,
                    Description TEXT,
                    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
                )";
                connection.Execute(createCategoryTable);

                // 创建图书信息表
                string createBooksTable = @"
                CREATE TABLE IF NOT EXISTS Books (
                    Id INT PRIMARY KEY AUTO_INCREMENT,
                    ISBN VARCHAR(20) NOT NULL UNIQUE,
                    Title VARCHAR(200) NOT NULL,
                    Author VARCHAR(100) NOT NULL,
                    Publisher VARCHAR(100) NOT NULL,
                    PublishDate DATE,
                    CategoryId INT NOT NULL,
                    Price DECIMAL(10,2),
                    Pages INT,
                    Description TEXT,
                    CoverImage VARCHAR(255),
                    Location VARCHAR(50),
                    Status ENUM('在馆', '借出', '预约', '维修') NOT NULL DEFAULT '在馆',
                    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
                )";
                connection.Execute(createBooksTable);

                // 创建图书副本表（实体书）
                string createBookItemsTable = @"
                CREATE TABLE IF NOT EXISTS BookItems (
                    Id INT PRIMARY KEY AUTO_INCREMENT,
                    BookId INT NOT NULL,
                    BarCode VARCHAR(50) NOT NULL UNIQUE,
                    Status ENUM('在馆', '借出', '预约', '维修', '丢失') NOT NULL DEFAULT '在馆',
                    AcquisitionDate DATE NOT NULL,
                    LastBorrowDate DATETIME NULL,
                    Notes TEXT,
                    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
                )";
                connection.Execute(createBookItemsTable);

                // 创建借阅记录表
                string createBorrowingsTable = @"
                CREATE TABLE IF NOT EXISTS Borrowings (
                    Id INT PRIMARY KEY AUTO_INCREMENT,
                    BookItemId INT NOT NULL,
                    UserId INT NOT NULL,
                    BorrowDate DATETIME NOT NULL,
                    DueDate DATETIME NOT NULL,
                    ReturnDate DATETIME NULL,
                    IsReturned TINYINT(1) NOT NULL DEFAULT 0,
                    FineAmount DECIMAL(10,2) DEFAULT 0,
                    Notes TEXT,
                    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
                )";
                connection.Execute(createBorrowingsTable);

                // 创建预约表
                string createReservationsTable = @"
                CREATE TABLE IF NOT EXISTS Reservations (
                    Id INT PRIMARY KEY AUTO_INCREMENT,
                    BookId INT NOT NULL,
                    UserId INT NOT NULL,
                    ReservationDate DATETIME NOT NULL,
                    ExpiryDate DATETIME NOT NULL,
                    Status ENUM('等待', '已完成', '已取消') NOT NULL DEFAULT '等待',
                    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
                )";
                connection.Execute(createReservationsTable);

                // 检查是否已存在管理员账户
                var adminExists = connection.QueryFirstOrDefault<int>("SELECT COUNT(1) FROM Users WHERE Username = 'admin'");
                
                if (adminExists == 0)
                {
                    // 添加默认管理员账户(密码:admin123)
                    var salt = Convert.ToBase64String(Guid.NewGuid().ToByteArray()).Substring(0, 16);
                    var passwordHash = BitConverter.ToString(
                        System.Security.Cryptography.SHA256.Create()
                        .ComputeHash(Encoding.UTF8.GetBytes("admin123" + salt)))
                        .Replace("-", "").ToLower();

                    connection.Execute(@"
                        INSERT INTO Users (Username, PasswordHash, Salt, Name, IsAdmin)
                        VALUES (@Username, @PasswordHash, @Salt, @Name, @IsAdmin)",
                        new { Username = "admin", PasswordHash = passwordHash, Salt = salt, Name = "系统管理员", IsAdmin = 1 });
                }

                connection.Close();
            }
        }

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        public static MySqlConnection GetConnection()
        {
            return new MySqlConnection(ConnectionString);
        }
    }
}
